package models

import (
	"encoding/gob"
	"time"

	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/sqlite"
)

type Model struct {
	ID        uint `gorm:"primary_key"`
	CreatedAt time.Time
	UpdatedAt time.Time
	//DeletedAt *time.Time `sql:"index"`
}

// 基础Model
type BaseModel struct {
	Model
	UserID uint
}

// 账本
type Book struct {
	BaseModel
	Name        string //账本名称
	Description string //账本描述
	Orderby     int    //排序
}

// 用户
type User struct {
	Model
	Telephone        string //手机号码
	Email            string //邮箱
	Password         string //密码
	EmailVerifyState int8   //邮箱验证状态
	NickName         string //昵称
}

// 用户账本关系表
type UserBook struct {
	Model
	UserID  uint // 用户ID
	BookID  uint // 账本ID
	Orderby int  //排序
}

// 账单表
type Bill struct {
	BaseModel
	Title       string    //标题
	Cate        string    //收入或者支出，收入为+，支出为-
	Account     float32   //发生金额
	OccureDate  time.Time //发生时间
	CasherID    uint      //付款人ID
	Total       int8      //总消费人数，默认显示账本关联用户数
	InvitorID   uint      //邀请人ID,谁邀请的费用算在谁的头上
	BookID      uint      //属于哪个账本的记录
	Detail      string    //消费明细
	Participant string    //参与消费的人员，使用$1#$2#方式存储
	Remark      string    //备注
	Avg         float32   `gorm:"-"` //平均值
	NickName    string    `gorm:"-"` //付款人
}

var db *gorm.DB

func init() {
	var err error
	db, err = gorm.Open("sqlite3", "test.db")
	if err != nil {
		panic("failed to connect database")
	}
	//defer db.Close()

	// Migrate the schema
	db.AutoMigrate(&Book{}, &User{}, &UserBook{}, &Bill{})
	gob.Register(&User{})
	db.LogMode(true)

	db.Model(&User{}).AddUniqueIndex("uk_telephone", "telephone")
	db.Model(&User{}).AddUniqueIndex("uk_email", "email")
}

// 添加用户
func InsertUser(telephone, email, pwd string) bool {
	user := User{
		Telephone:        telephone,
		Email:            email,
		EmailVerifyState: 0,
		Password:         pwd,
	}
	db.Create(&user)
	return user.ID > 0
}

// 用户登录系统
func Login(uname string) *User {
	var user User
	db.First(&user, "telephone = ? or email = ?", uname, uname)
	return &user
}

func GetUserByID(id uint) *User {
	var user User
	db.First(&user, "id = ?", id)
	return &user
}

// 添加账本
func InsertBook(name, description string, userID uint, orderby int) Book {
	book := Book{
		Name:        name,
		Description: description,
		Orderby:     orderby,
	}
	book.UserID = userID
	db.Create(&book)
	return book
}

// 添加账本用户关联关系
func InsertUserBook(userID, bookID uint) bool {
	userbook := UserBook{
		UserID: userID,
		BookID: bookID,
	}
	db.Create(&userbook)
	return userbook.ID > 0
}

// 根据手机号查询用户
func QueryUserByTelephone(telephone string) User {
	var user User
	db.First(&user, "telephone = ?", telephone)
	return user
}

// 根据邮箱查询用户
func QueryUserByEmail(email string) User {
	var user User
	db.First(&user, "email = ?", email)
	return user
}

// 添加消费记录
func InsertBill(title, cate, detail, participant, remark string, value float32, occurdate time.Time, casherID, invtorID, bookID, userID uint, total int8) bool {
	bill := Bill{
		Title:       title,
		Cate:        cate,
		Account:     value,
		OccureDate:  occurdate,
		CasherID:    casherID,
		Total:       total,
		InvitorID:   invtorID,
		BookID:      bookID,
		Detail:      detail,
		Participant: participant,
		Remark:      remark,
	}
	bill.UserID = userID
	db.Create(&bill)
	return bill.ID > 0
}

// 根据用户id获取账本列表
func SelectBookListByUserID(userID uint) ([]*Book, error) {
	sql := `select b.* from user_books a inner join books b on a.book_id = b.id inner join users u on a.user_id = u.id where u.id = ?`
	rows, err := db.Raw(sql, userID).Rows()
	defer rows.Close()
	if err != nil {
		return nil, err
	}
	books := make([]*Book, 0)
	for rows.Next() {
		var book Book
		db.ScanRows(rows, &book)
		books = append(books, &book)
	}
	return books, nil
}

func CountUserByBookID(bookID uint) int {
	var count int
	db.Model(&UserBook{}).Where("book_id = ?", bookID).Count(&count)
	return count
}

func SelectBillListByBookID(bookID uint) []*Bill {
	sql := `select a.*,u.nick_name from bills a inner join users u on a.casher_id = u.id where a.book_id = ? order by a.occure_date desc`
	rows, err := db.Raw(sql, bookID).Rows()
	defer rows.Close()
	if err != nil {
		return nil
	}
	bills := make([]*Bill, 0)
	for rows.Next() {
		var bill Bill
		db.ScanRows(rows, &bill)
		bills = append(bills, &bill)
	}
	return bills

}

func SelectUserBookByBookID(bookID uint) []*UserBook {
	var userbooks []*UserBook
	db.Find(&userbooks, "book_id = ?", bookID)
	return userbooks
}

func SumBill(bookID uint, total int, month, start, end string) float32 {

	//db.Raw("select * from bills where substr(date(occure_date),1,7) = ?")

	tempdb := db.Select("sum(account) ret").Model(&Bill{}).Where("book_id = ? and total = ?", bookID, total)
	if len(start) > 0 && len(end) > 0 {
		tempdb = tempdb.Where("substr(date(occure_date),1,10) between ? and ?", start, end)
	} else if len(month) > 0 {
		tempdb = tempdb.Where("substr(date(occure_date),1,7) = ?", month)
	}
	var ret float32
	tempdb.Row().Scan(&ret)
	return ret
}

func SelectInviteBill(bookID uint, month, start, end string) []*Bill {
	tempdb := db.Where("book_id = ?", bookID).Where("invitor_id > 0")
	if len(start) > 0 && len(end) > 0 {
		tempdb = tempdb.Where("substr(date(occure_date),1,10) between ? and ?", start, end)
	} else if len(month) > 0 {
		tempdb = tempdb.Where("substr(date(occure_date),1,7) = ?", month)
	}
	var bills []*Bill
	tempdb.Find(&bills)
	return bills
}

func SelectCasherCount(bookID uint, month, start, end string) []*Bill {
	tempdb := db.Select("casher_id,sum(account) account").Model(&Bill{}).Where("book_id = ?", bookID).Group("casher_id")
	if len(start) > 0 && len(end) > 0 {
		tempdb = tempdb.Where("substr(date(occure_date),1,10) between ? and ?", start, end)
	} else if len(month) > 0 {
		tempdb = tempdb.Where("substr(date(occure_date),1,7) = ?", month)
	}
	var bills []*Bill
	tempdb.Find(&bills)
	return bills
}

func SelectBillListByTotal(bookID uint, num int, month, start, end string) []*Bill {
	tempdb := db.Where("book_id = ?", bookID).Where("total = ?", num)
	if len(start) > 0 && len(end) > 0 {
		tempdb = tempdb.Where("substr(date(occure_date),1,10) between ? and ?", start, end)
	} else if len(month) > 0 {
		tempdb = tempdb.Where("substr(date(occure_date),1,7) = ?", month)
	}
	var bills []*Bill
	tempdb.Find(&bills)
	return bills
}

func DeleteBill(billid uint) bool {
	bill := Bill{}
	bill.ID = billid
	db.Delete(&bill)
	return true
}

func SelectUserListByBookID(bookID uint) []*User {
	sql := `select u.* from user_books ub inner join users u on ub.user_id = u.id where ub.book_id = ? order by ub.orderby asc`
	rows, err := db.Raw(sql, bookID).Rows()
	defer rows.Close()
	if err != nil {
		return nil
	}
	users := make([]*User, 0)
	for rows.Next() {
		var user User
		db.ScanRows(rows, &user)
		users = append(users, &user)
	}
	return users
}
